Using Correlation Matrices and Optimization to Add Practical Functionality to Spreadsheet Simulation for MBA-Level Quantitative Analysis Courses

A publication by Albritton, McMullen, and Gardiner (2003) indicates that while MBA-level Management Science/Operations Research courses have been deemphasized in recent years, they continue to survive—typically as part of a quantitative modeling class, which includes both statistical analysis and optimization components. The Albritton research effort presents survey results indicating that MBA-level quantitative modeling courses do provide intensive coverage of the traditional optimization approaches of linear programming formulation, integer programming formulation, and network optimization (survey respondents claiming coverage rates of 91%, 78%, and 77%, respectively). Unfortunately, spreadsheet simulation was covered by only 54% of the survey respondents, and the intensity of this coverage was less than average (on a 7-point Likert scale, the mean intensity was 3.25). This finding suggests that the coverage of spreadsheet simulation is essentially introductory—treating spreadsheet cells as assumptions, which behave according to some probability distribution. While treatment of spreadsheet cells as assumptions is the cornerstone of spreadsheet simulation, a few simple extensions can be employed to add practical functionality to the spreadsheet model. This teaching brief focuses on two of these simple extensions: (1) treating assumptions as having interdependence with each other; and (2) choosing values of decision variables to optimize objective functions, while certain inputs to the spreadsheet model are treated as correlated, stochastic assumptions. Many good quantitative business modeling texts cover spreadsheet simulation. To my knowledge, however, only one of them (Powell & Baker, 2004) mentions the treatment of assumptions as interdependent, and this is via a brief discussion only (no example). Additionally, I am aware of only three texts that detail optimization with stochastic assumptions (Powell & Baker, 2004; Ragsdale, 2004; Winston, 2004), and these optimization applications do not address the interrelationships between assumptions. Because of this void in textbook coverage